﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Model;

namespace MsDAL
{
    public class Sys5a01DAL
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public virtual SqlApp Add(Model.Sys5a01 sys5a01)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys5a01(");
            strSql.Append("listid ,plistid ,orderid ,colname ,headcn ,coltype ,isquery ,isshow ,oname ,fmt ,controltype ,maxlength ,digits ,defvalue ,editmode ,iskey ,notnull ,columnwidth ,colspan ,columnoption ,columnparam ,tp1 ,tp2 ,tp3 ,tp4 ,tp5 ,remark,tp6, tp7, tp8, tp9,tp10 )");
            strSql.Append(" values (");
            strSql.Append("@listid ,@plistid ,@orderid ,@colname ,@headcn ,@coltype ,@isquery ,@isshow ,@oname ,@fmt ,@controltype ,@maxlength ,@digits ,@defvalue ,@editmode ,@iskey ,@notnull ,@columnwidth ,@colspan ,@columnoption ,@columnparam ,@tp1 ,@tp2 ,@tp3 ,@tp4 ,@tp5 ,@remark,@tp6, @tp7, @tp8, @tp9,@tp10 )");

            SqlParameter[] parameters = {
				new SqlParameter("@listid",SqlDbType.NVarChar),
				new SqlParameter("@plistid",SqlDbType.NVarChar),
				new SqlParameter("@orderid",SqlDbType.Int),
				new SqlParameter("@colname",SqlDbType.NVarChar),
				new SqlParameter("@headcn",SqlDbType.NVarChar),
				new SqlParameter("@coltype",SqlDbType.NChar),
				new SqlParameter("@isquery",SqlDbType.NChar),
				new SqlParameter("@isshow",SqlDbType.NChar),
				new SqlParameter("@oname",SqlDbType.NVarChar),
				new SqlParameter("@fmt",SqlDbType.NVarChar),
				new SqlParameter("@controltype",SqlDbType.NChar),
				new SqlParameter("@maxlength",SqlDbType.Int),
				new SqlParameter("@digits",SqlDbType.Int),
				new SqlParameter("@defvalue",SqlDbType.NVarChar),
				new SqlParameter("@editmode",SqlDbType.NVarChar),
				new SqlParameter("@iskey",SqlDbType.NChar),
				new SqlParameter("@notnull",SqlDbType.NChar),
				new SqlParameter("@columnwidth",SqlDbType.Int),
				new SqlParameter("@colspan",SqlDbType.Int),
				new SqlParameter("@columnoption",SqlDbType.NVarChar),
				new SqlParameter("@columnparam",SqlDbType.NVarChar),
				new SqlParameter("@tp1",SqlDbType.NVarChar),
				new SqlParameter("@tp2",SqlDbType.NVarChar),
				new SqlParameter("@tp3",SqlDbType.NVarChar),
				new SqlParameter("@tp4",SqlDbType.NVarChar),
				new SqlParameter("@tp5",SqlDbType.NVarChar),
				new SqlParameter("@remark",SqlDbType.NVarChar),
                new SqlParameter("@tp6",SqlDbType.NVarChar),
                new SqlParameter("@tp7",SqlDbType.NVarChar),
                new SqlParameter("@tp8",SqlDbType.NVarChar),
                new SqlParameter("@tp9",SqlDbType.NVarChar),
                new SqlParameter("@tp10",SqlDbType.NVarChar)
				};
            parameters[0].Value = sys5a01.Listid;
            parameters[1].Value = sys5a01.Plistid;
            parameters[2].Value = sys5a01.Orderid;
            parameters[3].Value = sys5a01.Colname;
            parameters[4].Value = sys5a01.Headcn;
            parameters[5].Value = Convert.ToInt32(sys5a01.Coltype).ToString();
            parameters[6].Value = sys5a01.Isquery ? "Y" : "N";
            parameters[7].Value = sys5a01.Isshow ? "Y" : "N";
            parameters[8].Value = sys5a01.Oname;
            parameters[9].Value = sys5a01.Fmt;
            parameters[10].Value = Convert.ToInt32(sys5a01.Controltype).ToString();
            parameters[11].Value = sys5a01.Maxlength;
            parameters[12].Value = sys5a01.Digits;
            parameters[13].Value = sys5a01.Defvalue;
            parameters[14].Value = sys5a01.Editmode;
            parameters[15].Value = sys5a01.Iskey ? "Y" : "N";
            parameters[16].Value = sys5a01.Notnull ? "Y" : "N";
            parameters[17].Value = sys5a01.Columnwidth;
            parameters[18].Value = sys5a01.Colspan;
            parameters[19].Value = sys5a01.Columnoption;
            parameters[20].Value = sys5a01.Columnparam;
            parameters[21].Value = sys5a01.Tp1;
            parameters[22].Value = sys5a01.Tp2;
            parameters[23].Value = sys5a01.Tp3;
            parameters[24].Value = sys5a01.Tp4;
            parameters[25].Value = sys5a01.Tp5;
            parameters[26].Value = sys5a01.Remark;
            parameters[27].Value = sys5a01.Tp6;
            parameters[28].Value = sys5a01.Tp7;
            parameters[29].Value = sys5a01.Tp8;
            parameters[30].Value = sys5a01.Tp9;
            parameters[31].Value = sys5a01.Tp10;

            return new SqlApp(strSql.ToString(), new ArrayList(parameters), false, "数据保存失败！");
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public virtual SqlApp Update(Model.Sys5a01 sys5a01)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys5a01 set ");
            strSql.Append("plistid=@plistid ,orderid=@orderid ,colname=@colname ,headcn=@headcn ,coltype=@coltype ,isquery=@isquery ,isshow=@isshow ,oname=@oname ,fmt=@fmt ,controltype=@controltype ,maxlength=@maxlength ,digits=@digits ,defvalue=@defvalue ,editmode=@editmode ,iskey=@iskey ,notnull=@notnull ,columnwidth=@columnwidth ,colspan=@colspan ,columnoption=@columnoption ,columnparam=@columnparam ,tp1=@tp1 ,tp2=@tp2 ,tp3=@tp3 ,tp4=@tp4 ,tp5=@tp5 ,remark=@remark,tp6=@tp6,tp7=@tp7,tp8=@tp8,tp9=@tp9,tp10=@tp10 ");
            strSql.Append("where listid=@listid ");
            SqlParameter[] parameters = {
				new SqlParameter("@listid", SqlDbType.NVarChar),
				new SqlParameter("@plistid", SqlDbType.NVarChar),
				new SqlParameter("@orderid", SqlDbType.Int),
				new SqlParameter("@colname", SqlDbType.NVarChar),
				new SqlParameter("@headcn", SqlDbType.NVarChar),
				new SqlParameter("@coltype", SqlDbType.NChar),
				new SqlParameter("@isquery", SqlDbType.NChar),
				new SqlParameter("@isshow", SqlDbType.NChar),
				new SqlParameter("@oname", SqlDbType.NVarChar),
				new SqlParameter("@fmt", SqlDbType.NVarChar),
				new SqlParameter("@controltype", SqlDbType.NChar),
				new SqlParameter("@maxlength", SqlDbType.Int),
				new SqlParameter("@digits", SqlDbType.Int),
				new SqlParameter("@defvalue", SqlDbType.NVarChar),
				new SqlParameter("@editmode", SqlDbType.NVarChar),
				new SqlParameter("@iskey", SqlDbType.NChar),
				new SqlParameter("@notnull", SqlDbType.NChar),
				new SqlParameter("@columnwidth", SqlDbType.Int),
				new SqlParameter("@colspan", SqlDbType.Int),
				new SqlParameter("@columnoption", SqlDbType.NVarChar),
				new SqlParameter("@columnparam", SqlDbType.NVarChar),
				new SqlParameter("@tp1", SqlDbType.NVarChar),
				new SqlParameter("@tp2", SqlDbType.NVarChar),
				new SqlParameter("@tp3", SqlDbType.NVarChar),
				new SqlParameter("@tp4", SqlDbType.NVarChar),
				new SqlParameter("@tp5", SqlDbType.NVarChar),
				new SqlParameter("@remark", SqlDbType.NVarChar),
                new SqlParameter("@tp6",SqlDbType.NVarChar),
                new SqlParameter("@tp7",SqlDbType.NVarChar),
                new SqlParameter("@tp8",SqlDbType.NVarChar),
                new SqlParameter("@tp9",SqlDbType.NVarChar),
                new SqlParameter("@tp10",SqlDbType.NVarChar)
				};
            parameters[0].Value = sys5a01.Listid;
            parameters[1].Value = sys5a01.Plistid;
            parameters[2].Value = sys5a01.Orderid;
            parameters[3].Value = sys5a01.Colname;
            parameters[4].Value = sys5a01.Headcn;
            parameters[5].Value = Convert.ToInt32(sys5a01.Coltype).ToString();
            parameters[6].Value = sys5a01.Isquery ? "Y" : "N";
            parameters[7].Value = sys5a01.Isshow ? "Y" : "N";
            parameters[8].Value = sys5a01.Oname;
            parameters[9].Value = sys5a01.Fmt;
            parameters[10].Value = Convert.ToInt32(sys5a01.Controltype).ToString();
            parameters[11].Value = sys5a01.Maxlength;
            parameters[12].Value = sys5a01.Digits;
            parameters[13].Value = sys5a01.Defvalue;
            parameters[14].Value = sys5a01.Editmode;
            parameters[15].Value = sys5a01.Iskey ? "Y" : "N";
            parameters[16].Value = sys5a01.Notnull ? "Y" : "N";
            parameters[17].Value = sys5a01.Columnwidth;
            parameters[18].Value = sys5a01.Colspan;
            parameters[19].Value = sys5a01.Columnoption;
            parameters[20].Value = sys5a01.Columnparam;
            parameters[21].Value = sys5a01.Tp1;
            parameters[22].Value = sys5a01.Tp2;
            parameters[23].Value = sys5a01.Tp3;
            parameters[24].Value = sys5a01.Tp4;
            parameters[25].Value = sys5a01.Tp5;
            parameters[26].Value = sys5a01.Remark;
            parameters[27].Value = sys5a01.Tp6;
            parameters[28].Value = sys5a01.Tp7;
            parameters[29].Value = sys5a01.Tp8;
            parameters[30].Value = sys5a01.Tp9;
            parameters[31].Value = sys5a01.Tp10;

            return new SqlApp(strSql.ToString(), new ArrayList(parameters), false, "数据保存失败！");
        }

        public virtual SqlApp Delete(string listid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM  Sys5a01  ");
            strSql.Append("WHERE listid=@listid ");
            SqlParameter[] parameters = {
				new SqlParameter("@listid", SqlDbType.NVarChar)
				};
            parameters[0].Value = listid;

            return new SqlApp(strSql.ToString(), new ArrayList(parameters), false, "数据删除失败！");
        }

        /// <summary>
        /// 根据Sys5a00的ID得到控件列表
        /// </summary>
        /// <param name="p_listid"></param>
        /// <returns></returns>
        public DataTable GetList(string p_listid)
        {
            string sql = @"SELECT listid ,plistid ,orderid ,colname ,headcn ,coltype ,isquery ,isshow ,oname ,fmt ,controltype ,maxlength ,
digits ,defvalue ,editmode ,iskey ,notnull ,columnwidth ,colspan ,columnoption ,
columnparam ,tp1 ,tp2 ,tp3 ,tp4 ,tp5 ,remark ,tp6, tp7, tp8, tp9, tp10
FROM Sys5a01 WHERE plistid = @plistid ORDER BY orderid ASC";

            return DBHelper.GetData(sql, new SqlParameter("@plistid", p_listid));
        }

        /// <summary>
        /// 得到字段列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="ctl"></param>
        /// <returns></returns>
        public DataTable GetList(string page, string ctl)
        {
            string sql = @"SELECT listid ,plistid ,orderid ,colname ,headcn ,coltype ,isquery ,isshow ,oname ,fmt ,controltype ,maxlength ,
digits ,defvalue ,editmode ,iskey ,notnull ,columnwidth ,colspan ,columnoption ,
columnparam ,tp1 ,tp2 ,tp3 ,tp4 ,tp5 ,remark ,tp6, tp7, tp8, tp9, tp10
FROM Sys5a01 WHERE plistid = (SELECT b.listid FROM Sys5a00 b WHERE lower(b.pagename)=lower(@a) AND lower(b.ctlname) =lower(@b) ) 
AND (tp5 is null  or tp5 = '')
ORDER BY orderid ASC ";

            return DBHelper.GetData(sql, new SqlParameter("@a", page), new SqlParameter("@b", ctl));
        }


        public virtual SqlApp UpdateWidth(string listid, int width)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE  Sys5a01 SET columnwidth = @width ");
            strSql.Append("WHERE listid=@listid ");
            SqlParameter[] parameters = {
				    new SqlParameter("@listid", SqlDbType.NVarChar),
                    new SqlParameter("width", SqlDbType.Int)
				};
            parameters[0].Value = listid;
            parameters[1].Value = width;

            return new SqlApp(strSql.ToString(), new ArrayList(parameters), false, "更新宽度失败！");
        }
    }
}
